package webSiteDataStore;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Feedback;

public class FeedbackData 
{
    private static final Logger theLogger = Logger.getLogger(FeedbackData.class.getName());
    
    public static boolean insertFeedback(Feedback feedback) 
    {
        if (feedback == null)
        {
            return false;
        }

        boolean             success             = false;
        Connection          connection          = null;
        PreparedStatement   preparedStatement   = null;

        try
        {
            connection = ConnectionPool.getConnection();
            String sqlStatement = "INSERT INTO feedback (subject, text, time, signature) VALUES(?,?,?,?);";
            preparedStatement = connection.prepareStatement(sqlStatement);
            preparedStatement.setString(1, feedback.getSubject());
            preparedStatement.setString(2, feedback.getText());
            
            // From http://www.java2s.com/Tutorial/Java/0040__Data-Type/ConvertfromajavautilDateObjecttoajavasqlDateObject.htm
            java.util.Date utilDate = feedback.getTime();
            java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
    
            preparedStatement.setDate(3, sqlDate);
            preparedStatement.setString(4, feedback.getSignature());
            int rowsAffected = preparedStatement.executeUpdate();
            success = (rowsAffected == 1);
            if (rowsAffected == 0)
            {
                String msg = "Zero rows affected when inserting feedback";
                theLogger.log(Level.SEVERE, msg.toString());
            }
            if (rowsAffected > 1)
            {
                String msg = "Multiple rows affected when inserting feedback";
                theLogger.log(Level.SEVERE, msg.toString());
            }
        }
        catch(SQLException e)
        {
            theLogger.log(Level.SEVERE, "Database access failure in FeedbackData#insertFeedback", e);
        }
        finally
        {
            SafeFreeResource.closeStatement(preparedStatement);
            SafeFreeResource.closeConnection(connection);
        }
        return success;
    }  
    
public static ArrayList<model.Feedback> loadFeedback(int feedbackId)
    {
        Connection                  connection          = null;
        PreparedStatement           preparedStatement   = null;
        ArrayList<model.Feedback>   feedbacksList       = null;

        try
        {
            connection = ConnectionPool.getConnection();
            String sqlStatement = "SELECT * FROM Feedback WHERE feedbackId = ? ;";
            preparedStatement = connection.prepareStatement(sqlStatement);
            preparedStatement.setInt(1, feedbackId);
            feedbacksList = doLoadFeedback(preparedStatement);
        }
        catch(SQLException e)
        {
            theLogger.log(Level.SEVERE, "Database access failure in FeedbackData#loadFeedback", e);
        }
        finally
        {
            SafeFreeResource.closeStatement(preparedStatement);
            SafeFreeResource.closeConnection(connection);
        }
        
        return feedbacksList;
    }    
    
    public static ArrayList<model.Feedback> loadFeedback(String signature)
    {
        Connection                  connection          = null;
        PreparedStatement           preparedStatement   = null;
        ArrayList<model.Feedback>   feedbacksList       = null;

        try
        {
            connection = ConnectionPool.getConnection();
            String sqlStatement = "SELECT * FROM Feedback WHERE signature = ? ;";
            preparedStatement = connection.prepareStatement(sqlStatement);
            preparedStatement.setString(1, signature);
            feedbacksList = doLoadFeedback(preparedStatement);
        }
        catch(SQLException e)
        {
            theLogger.log(Level.SEVERE, "Database access failure in FeedbackData#loadFeedback", e);
        }
        finally
        {
            SafeFreeResource.closeStatement(preparedStatement);
            SafeFreeResource.closeConnection(connection);
        }
        
        return feedbacksList;
    }
    
   public static ArrayList<model.Feedback> doLoadFeedback(PreparedStatement preparedStatement)
    {
        ResultSet                   resultSet     = null;
        model.Feedback              feedback      = null;
        ArrayList<model.Feedback>   feedbacksList = null;

        try
        {
            preparedStatement.executeQuery();
            resultSet = preparedStatement.getResultSet();
            feedbacksList = new ArrayList<model.Feedback>();
            while (resultSet.next())
            {
                feedback =  model.Feedback.create().setSubject(resultSet.getString("subject"))
                                                    .setText(resultSet.getString("text"))
                                                    .setSignature(resultSet.getString("signature"))
                                                    .setTime(resultSet.getDate("time"))
                                                    .setId(resultSet.getInt("feedbackId"));
                feedbacksList.add(feedback);
            }            
        }
        catch(SQLException e)
        {
            theLogger.log(Level.SEVERE, "Database access failure in FeedbackData#doLoadFeedback", e);
        }
        finally
        {
            SafeFreeResource.closeResultSet(resultSet);
        }
        
        return feedbacksList;
    }    
}
